package com.Demo;

import utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.Scanner;

public class Demo04_select {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select id, name from class";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();

            while (resultSet.next()) {
                long id = resultSet.getLong("id");
                String name = resultSet.getString("name");
                System.out.println(MessageFormat.format("id: {0}, name: {1}", id, name));
            }

            String sql1 = "select s.id, s.name, s.sno, s.age, s.gender, s.enroll_data, s.class_id, c.id cId, c.name cName"
                    + " from student s, class c where s.class_id = c.id and c.name = ?";
            statement = connection.prepareStatement(sql1);

            System.out.println("请输入要查询的班级名称:");
            Scanner scanner = new Scanner(System.in);
            String inputName = scanner.next();

            statement.setString(1, inputName);
            resultSet = statement.executeQuery();

            while (resultSet.next()) {
                long id = resultSet.getLong("id");
                String name = resultSet.getString("name");
                String sno = resultSet.getString("sno");
                int age = resultSet.getInt("age");
                byte gender = resultSet.getByte("gender");
                String enroll_data = resultSet.getString("enroll_data");
                long class_id = resultSet.getLong("class_id");
                long cId = resultSet.getLong("cId");
                String cName = resultSet.getString("cName");
                System.out.println(MessageFormat.format("id: {0}, name: {1}, sno: {2}, age: {3}" +
                        ", gender: {4}, enroll_data: {5}, class_id: {6}, cId: {7}, cName: {8}"
                        , id, name, sno, age, gender, enroll_data, class_id, cId, cName));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeConnection(resultSet, statement, connection);
        }




    }
}
